<?php
namespace Sevstudio\SevMysqlBackup;

use \PDO;
use \PDOException;

//mysql数据备份还原工具
class Helper{
    
    private $con = null;
	private $config = null;
	private $tables = []; //当前配置数据库表集合
    private $handleWriter = null;
		
	function __construct($config){
		$this->config = [
			'host' => $config['host'],
			'port' => $config['port'],
			'uid' => $config['uid'],
			'pwd' => $config['pwd'],
			'dbname' => $config['dbname'],
			'charset' => isset($config['charset']) ? $config['charset'] : 'utf8',
			'prefix' => isset($config['prefix']) ? $config['prefix'] : '',
            'usebuffer' => false,
			'debug' => isset($config['debug']) ? $config['debug'] === true || $config['debug'] == 'true' : true,
		];
        $this->Connect();
	}
	
	//连接数据库
	//参数：是否静默连接，仅返回成功失败，不抛出异常
	private function Connect($silent=false) : bool {
		try{
            $param = [
                PDO::MYSQL_ATTR_INIT_COMMAND => "set names '{$this->config['charset']}'"
				,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                ,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false //非缓存查询
            ];
			$this->con = new PDO(
				"mysql:host={$this->config['host']};port={$this->config['port']};dbname={$this->config['dbname']}",
				$this->config['uid'],
				$this->config['pwd'],
				$param
			);
		}catch(\PDOException $ex){
			$this->con = null;
			if($silent){
				return false;
			}
			if($this->config['debug']){
				throw $ex;
			}
			return false;
		}
		//读取所有表名
		$tables = $this->ShowTables();
		if($this->config['prefix'] == ''){
			$this->tables = $tables;
		}else{
			$len = strlen($this->config['prefix']);
			foreach($tables as $tb){
				if(substr($tb,0,$len) == $this->config['prefix']){
					$this->tables[] = $tb;
				}
			}
		}
		return true;
	}
        
    public function Backup($save_dir,$file_prefix = '') : array {
        $save_dir = str_replace("\\",\DIRECTORY_SEPARATOR,$save_dir);
        $save_dir = rtrim($save_dir,\DIRECTORY_SEPARATOR);
        $save_file_name = $file_prefix . uniqid() . '.sql';
        $save_file = $save_dir . \DIRECTORY_SEPARATOR . $save_file_name;
        if(!file_exists($save_dir) && !@mkdir($save_dir,0777,true)){
            return ['code'=>-1,'info'=>'请检查文件夹写入权限'];
        }
        $this->handleWriter = @fopen($save_file,'w');
        if($this->handleWriter === false){
            return ['code'=>-1,'info'=>'请检查文件写入权限'];
        }
        try{
            $this->EchoLine("/*\r\n Sevstudio MySQLBackup\r\n (v:sevstudio)\r\n Date: ". date('Y-m-d H:i:s') ."\r\n*/",2);
            $this->EchoLine("SET NAMES '{$this->config['charset']}';",2);
            $this->EchoLine("SET FOREIGN_KEY_CHECKS = 0;",2);
            //遍历表
            foreach($this->tables as $table){
                $tableCreate = $this->Find('SHOW CREATE TABLE `'. $table .'`;');
                if(!isset($tableCreate['Create Table'])){
                    throw new \Exception('备份失败，无法获取到表['. $table .']的创建信息');
                }
                //删表语句
                $this->EchoLine("DROP TABLE IF EXISTS `{$table}`;",1);
                //建表语句
                $this->EchoLine("{$tableCreate['Create Table']};",2);
                //数据主体
                $dataList = $this->Select2("SELECT * FROM `{$table}`");
                foreach($dataList as $row){
                    $line = 'INSERT INTO `'. $table .'` VALUES (';
                    $values = '';
                    foreach($row as $r){
                        if(is_null($r)){
                            $values.= 'null,';
                        }else if(is_bool($r)){
                            $values.= ($r ? 'true' : 'false') . ',';
                        }else if(is_numeric($r)){
                            $values.= $r . ',';
                        }else if(is_string($r)){
                            $values.= $this->con->quote($r) . ',';
                        }else{
                            //$values[] = addslashes($r);
                            $values.= $this->con->quote($r,\PDO::PARAM_LOB) . ',';
                        }
                    }
                    $values = rtrim($values,',');
                    $line.= $values . ');';
                    $this->EchoLine($line);
                }
                $this->EchoLine('');
            }
            $this->EchoLine("SET FOREIGN_KEY_CHECKS = 1;");
            return ['code'=>0,'info'=>'备份成功','file_name' => $save_file_name];
        }catch(\Exception $ex){
            //echo $ex->getMessage();
        }finally{
            @fclose($this->handleWriter);
            $this->handleWriter = null;
        }
        @unlink($save_file);
        return ['code'=>-1,'info'=>'备份失败'];
    }
    
    public function Restore($sqlfile) : array {
        if(!file_exists($sqlfile)){
            return ['code'=>-1,'info'=>'备份文件不存在'];
        }
        $fp = @fopen($sqlfile,'r');
        if($fp){
            try{
                $cache = '';
                while(($buffer = fgets($fp, 1000)) !== false){
                    list($data,$cache) = $this->FixLine($buffer,";\r\n",$cache);
                    foreach($data as $sql){
                        $this->Excute($sql);
                    }
                    $data = null;
                }
                if(!feof($fp)){
                    //没有到达文件尾部，说明fgets失败了
                    throw new \Exception("读取失败");
                }
                return ['code'=>0,'info'=>'还原成功'];
            }catch(\PDOException $pdoex){
                //echo $pdoex->getMessage();
            }catch(\Exception $ex){
                //echo $ex->getMessage();
            }finally{
                @fclose($fp);
            }
        }else{
            //echo '打开文件失败' . PHP_EOL;
        }
        return ['code'=>-1,'info'=>'还原失败'];
    }
    
    private function FixLine($content,$str_split,$cache) : array {
        $new_content = $cache . $content;
        $arr = explode($str_split,$new_content);
        if(count($arr) <= 1){
            //没有分隔符
            $cache = $new_content;
            $data = [];
        }else{
            //结尾是分隔符，那么最后一个是空字符串
            //结尾非分隔符，那么租后一个是不完整的语句
            $cache = array_pop($arr);
            $data = $arr;
        }
        return [$data,$cache];
    }
    
    private function EchoLine($content,$break_count = 1){
        $break_str = $break_count === 1 ? "\r\n" : "\r\n\r\n";
        if(@fwrite($this->handleWriter,$content . $break_str) === false){
            throw new \Exception('备份失败');
        }
    }

	//原生查询
	private function Query($sql,array $arr = []){
		$sql = $this->FixSQL($sql);
		$dp = $this->con->prepare($sql);//PDO::ERRMODE_EXCEPTION下，失败则抛出异常
        for($i=1;$i<=count($arr);$i++){
            $dp->bindParam($i,$arr[$i-1]);
        }
		$dp->execute(); //PDO::ERRMODE_EXCEPTION下，失败则抛出异常
		$data = $dp->fetchAll(\PDO::FETCH_ASSOC); //PDO::ERRMODE_EXCEPTION下，失败则抛出异常
		return $data ?: [];
	}
	
	//返回受影响行数(insert,delete,update)
	private function Excute($sql){
		if($this->con == null){
			return 0;
		}
		$sql = $this->FixSQL($sql);
        try{
            $dp = $this->con->prepare($sql);
            $dp->execute();
            return $dp->rowCount(); //失败也是会抛出异常
        }catch(\PDOException $pdoex){
            $errCode = $pdoex->errorInfo[1] ?? '';
            if($errCode == 1065){
                //空查询
                return 0;
            }
            throw $pdoex; 
        }
	}
	
	//获取库中所有表
	private function ShowTables() : array {
		if($this->con == null){
			return [];
		}
		$list = $this->Query('SHOW TABLES');
		return array_column($list ?: [],'Tables_in_'.$this->config['dbname']);
	}
	
	//查看表结构的详细信息
	//参数：表名(不会自动加上表前缀)
	private function DescTable($table){
		$list = $this->Query($this->FixSQL('DESC ' . $table));
		return $list ?: null;
	}

	//处理SQL语句的表前缀
	private function FixSQL($sql){
		if(empty($this->tables)){
			return $sql;
		}
		$tables = $this->tables;
		$prev = $this->config['prefix'];
		$res = preg_replace_callback('/#__([0-9a-z_]+)/i',function($match) use ($tables,$prev){
			if(in_array($prev . $match[1],$tables)){
				return $prev . $match[1];
			}
			return $match[0];
		},$sql);
		return $res;
	}
    
    private function Find($sql){
		if($this->con == null){
			return null;
		}
		$sql = $this->FixSQL($sql);
		$dp = $this->con->prepare($sql);
		$dp->execute();
		return $dp->fetch(\PDO::FETCH_ASSOC);
	}

	//yield方式查询大表，需要配置中关闭缓存查询，否则容易引起内存超限
	private function Select2($sql){
		if($this->con == null){
			return;
		}
		$sql = $this->FixSQL($sql);
		$dp = $this->con->prepare($sql);
		$dp->execute();
		//while($row = $dp->fetch(\PDO::FETCH_ASSOC)){
		while($row = $dp->fetch(\PDO::FETCH_NUM)){ //数字索引下标
			yield $row;
		}
	}

    
}